﻿
-- 人员班组信息
CREATE view [dbo].[vRyBzInfo]
as
select Ry_Info.*,aa.* from 
Ry_Info
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join
(select t2.bzid,t2.bzfid,t1.bzmc+'\'+t2.bzmc bzmc,t2.bz 
from Banz_Info t1
inner join Banz_Info t2 on(t1.bzid=t2.bzfid)
union
select * from dbo.Banz_Info where bzfid is null
) aa on(Bzry_Info.bzid=aa.bzid)